<?php
/**
 * Description:房源信息导入相关
 * User: wuhao
 * Date: 2017-06-21
 * Time: 14:16
 */
namespace app\backend\controller;
use app\backend\controller\traits\AuthManager;
use app\backend\model\Building;
use app\backend\model\ExcelFile;
use think\Controller;

class BuildingImportController extends Controller
{
    use AuthManager;
    private $_memberId = null;
    private $_dtype    = null;
    private $_pdtype   = null;
    private $_pmid     = null;
    private $_allAuth  = [];
    public function __construct()
    {
        parent::__construct();
        $memberInfo = $this->getLoginInfo();
        $this->_memberId = $memberInfo['mid'];
        $this->_dtype    = $memberInfo['dtype'];
        $this->_pdtype   = $memberInfo['p_dtype'];
        $this->_pmid     = $memberInfo['p_mid'];
        $this->_allAuth  = $this->getPower($this->_memberId, $this->_dtype);
    }

    /**
     * 获取房源模型
     * @return Building
     */
    public function getBuildingModel()
    {
        static $model;
        if (!$model) {
            $model = new Building();
        }
        return $model;
    }

    /**
     * 导出房源模板
     * @date   2017-06-21
     * @author wuhao
     *
     */
    public function exportModel()
    {
        if (!in_array($this->_pdtype, [3, 4])) {
            $this->apiReturn(400, [], '无权操作');
        }
        $buildingId = I('building_id', '', 'intval');
        if (!$buildingId) {
            $this->apiReturn(400, [], '请选择楼宇');
        }
        $where = ['id' => $buildingId, 'mid' => $this->_pmid, 'is_delete' => 0];
        $buildingInfo = $this->getBuildingModel()->getBuildingList($where, '*');
        if (!$buildingInfo) {
            $this->apiReturn(400, [], '楼宇有误');
        }

        $fileName = '批量房源';
        $title = ['A1' => '楼宇id', 'B1' => '楼栋', 'C1' => '楼层', 'D1' => '房号', 'E1' => '面积（㎡）', 'F1' => '房租（元/月）',
                  'G1' => '物业（元/㎡）', 'H1' => '水（元/吨）', 'I1' => '电（元/度）', 'J1' => '公摊水（元/吨）', 'K1' => '公摊电（元/度）', 'L1' => '其他（元/月）'];
        $width = ['A' => 15, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 15, 'F' => 15, 'G' => 15, 'H' => 15, 'I' => 15, 'J' => 15, 'K' => 15, 'L' => 15];
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($fileName);
        $objPHPExcel->getActiveSheet()->getStyle( 'A1:G1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
        foreach ($width as $a => $b) {//设置每列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
        }
        foreach ($title as $c => $d) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
        }

        //合并第二行单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A2:L2');
        //设置备注
        $comment = '备注（导入时请不要删除这栏）：1. 红色为必填项';
        $objPHPExcel->getActiveSheet()->setCellValue('A2', $comment);
        //设置100个
        //$objPHPExcel->getActiveSheet()->setCellValue('A3', $buildingId);
        for ($i = 0; $i<100; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.($i+3), $buildingId);
        }
        $saveName = $fileName . date('Ymd') . '.xls';
        ob_end_clean();
        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Content-Type:application/force-download');
        header('Content-Type:application/vnd.ms-execl');
        header('Content-Type:application/octet-stream');
        header('Content-Type:application/download');
        header("Content-Disposition:attachment;filename={$saveName}");
        header('Content-Transfer-Encoding:binary');
        $objWriter->save('php://output');
    }

    /**
     * 读取并验证excel数据
     * @date   2017-06-27
     * @author wuhao
     *
     * @param string  $file  文件地址
     * @param string  $filename 文件名
     */
    public function readExcel()
    {
        if(!$this->checkAuth($this->_dtype, 'import')) {
            $this->apiReturn(400, [], '无权操作');
        }
        $file = I('file', '', 'strval');
        $filename = I('filename', '', 'strval');
        //$file = "http://zft.12301.local/excel/property_manager/access/60/14967147734451.xls";
        //$filename = '物业水电管理.xls';
        if (!$file) {
            $this->apiReturn(400, [], '文件地址有误');
        }

        //获取这个文件的地址,将主域名替换成目录名
        $fileInfo = str_replace(MAIN_DOMAIN, EXCEL_REMOVE_DIR, $file);
        $excel = pathinfo($fileInfo);
        //dd($excel);
        // 文件类型校验
        if (!in_array($excel['extension'], ['xls', 'xlsx', 'csv'])) {
            $this->apiReturn(400, [], '不是excel文件');
        }

        $e = file_get_contents($fileInfo);
        if (!$e) {
            $this->apiReturn(400, [], '文件获取失败');
        }

        //先获取excel数据
        $res = $this->_getData($fileInfo);
        if (!$res) {
            unlink($fileInfo);//删除这个文件
            $this->apiReturn(400, [], '所传的文件数据为空，请重新上传');
        }

        $countTotal = count($res);
        $fileModel  = new ExcelFile();
        $fileId     = $fileModel->addFile($filename, $this->_pmid, $file, date('Y-m-d H:i:s'), $countTotal, 3, 4);
        if (!$fileId) {
            zft_log('property_manager', $fileModel->getLastSql());
            $this->apiReturn(400, [], '记录文件失败');
        }

        //验证数据3秒后后台跑
        sleep(3);
        set_time_limit(0);
        $resData     = $this->_checkData($res);
        $accessData  = $resData['access'];
        $wrongData   = $resData['wrong'];
        $countAccess = count($accessData);
        $countWrong  = count($wrongData);
        $total       = $countWrong + $countAccess;

        $tmpNum = 1000;
        if (!$wrongData) {
            //生成房源信息
            foreach ($accessData as $v) {
                $data = [
                    'building_id'   => $v[0],
                    'number'        => $v[1],
                    'floor'         => $v[2],
                    'room'          => $v[3],
                    'price_type'    => 0, //默认按月
                    'area'          => $v[4],
                    'price'         => fen($v[5]),
                    'property'      => fen($v[6]),
                    'price_water'   => fen($v[7]),
                    'price_power'   => fen($v[8]),
                    'fee_water'     => fen($v[9]),
                    'fee_power'     => fen($v[10]),
                    'others'        => fen($v[11]),
                    'mid'           => $this->_pmid,
                    'house_number'  => $v[0].time().$tmpNum,
                    'time'          => time(),
                ];
                $tmpNum++;
                $id = $this->getBuildingModel()->addHouse($data);
                if (!$id) {
                    zft_log('house_add', $this->getMemberModel()->getLastSql());
                }
                unset($data, $id);
            }

            //没有错误的数据
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 1,
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('house_import', $fileModel->getLastSql());
            }
            echo 1;
            exit();
        } else {
            //保存错误数据到模板
            $wrongUrl = $this->_saveWrongModel($accessData, $wrongData);
            $updateData = [
                'record'          => $total,
                'access_record'   => $countAccess,
                'validate_status' => 1,
                'status'          => 0,
                'wrong_url'       => $wrongUrl
            ];
            $res = $fileModel->updateFile($fileId, $updateData);
            if (!$res) {
                zft_log('member_import', $fileModel->getLastSql());
            }
            echo 2;
            exit();
        }
    }

    /**
     * 读取excel数据
     * @date   2017-06-06
     * @author wuhao
     *
     * @param string  $fileInfo  文件的绝对地址
     * @return array
     */
    private function _getData($fileInfo)
    {
        $objPHPExcel = load_excel();
        $reader = \PHPExcel_IOFactory::createReader('Excel5');
        $phpExcel = $reader->load($fileInfo,$encode='utf-8');

        //读取excel数据
        $sheet = $phpExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数

        $tmpData = [];
        for ($row = 3; $row <= $highestRow; $row++) {//内容是以第3行开始
            for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
                $tmp = $sheet->getCell($column.$row)->getValue(); //读取到数组中
                if (is_object($tmp)) {
                    $tmp = $tmp->__toString();
                }

                $tmpData[$row][] = trim($tmp); //过滤空串，读取到数组中
                unset($tmp);
            }
        }
        $tmpData = array_merge($tmpData);
        $data = $tmpData;
        unset($dataset, $highestRow, $highestColumm);

        $res = [];
        foreach ($data as $k => $v) {
            if (!$v[1] && !$v[2] && !$v[3]) {
                //楼栋楼层房号都为空，过滤
                continue;
            } else {
                $res[] = $v;
            }
        }
        return $res;
    }

    /**
     * 验证数据,返回正确的和错误的
     * @date   2017-06-26
     * @author wuhao
     *
     * @param array  $data  要验证的数据
     * @return array ['access' => array, 'wrong' => array]
     */
    private function _checkData($data)
    {
        $accessData = $wrongData = [];
        $wrongComm  = [
            0 => '楼宇id有误',
            1 => '楼栋有误',
            2 => '楼层有误',
            3 => '房号不能为空',
            4 => '面积有误',
            5 => '房租有误',
            6 => '物业费有误',
            7 => '水单价有误',
            8 => '电单价有误',
            9 => '公摊水有误',
            10 => '公摊电有误',
            11 => '其他费用有误',
            12 => '楼宇id不存在',
            13 => '房源已经存在',
        ];

        //再判断格式
        foreach ($data as $k => $v) {
            //基本的判断
            if (!$v[0] || !is_numeric($v[0])) {//楼宇id
                $data[$k]['comment'] = $wrongComm[0];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[1] ) {//楼栋
                $data[$k]['comment'] = $wrongComm[1];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[2]) {//楼层
                $data[$k]['comment'] = $wrongComm[2];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[3]) {//房号
                $data[$k]['comment'] = $wrongComm[3];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[4] || !is_numeric($v[4])) {//面积
                $data[$k]['comment'] = $wrongComm[4];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[5] || !is_numeric($v[5])) {//房租
                $data[$k]['comment'] = $wrongComm[5];
                $wrongData[]  = $data[$k];
                continue;
            }
            if (!$v[6] || !is_numeric($v[6])) {//物业费
                $data[$k]['comment'] = $wrongComm[6];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[7] && !is_numeric($v[7])) {//
                $data[$k]['comment'] = $wrongComm[7];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[8] && !is_numeric($v[8])) {//
                $data[$k]['comment'] = $wrongComm[8];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[9] && !is_numeric($v[9])) {//
                $data[$k]['comment'] = $wrongComm[9];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[10] && !is_numeric($v[10])) {//
                $data[$k]['comment'] = $wrongComm[10];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($v[11] && !is_numeric($v[11])) {//
                $data[$k]['comment'] = $wrongComm[11];
                $wrongData[]  = $data[$k];
                continue;
            }

            //其他的判断
            if (!$this->getBuildingModel()->getBuildingList(['id' => $v[0], 'mid' => $this->_pmid, 'is_delete'=>0], 'id')) {//判断楼宇是否存在
                $data[$k]['comment'] = $wrongComm[12];
                $wrongData[]  = $data[$k];
                continue;
            }
            if ($this->getBuildingModel()->checkHouseOwner($v[0], $v[1], $v[2], $v[3])) {//检测房源唯一性
                $data[$k]['comment'] = $wrongComm[13];
                $wrongData[]  = $data[$k];
                continue;
            }
            $accessData[] = $data[$k];
        }

        $res = [
            'access' => $accessData,
            'wrong'  => $wrongData,
        ];
        return $res;
    }

    /**
     * 导出存在错误的数据模板
     * @date   2017-06-26
     * @author wuhao
     *
     * @param array       $accessData   正确的数据
     * @param array       $wrongData    错误的数据
     * @return string 文件地址
     */
    private function _saveWrongModel($accessData, $wrongData)
    {
        //设置导出样式等
        $objPHPExcel     = load_excel();
        $objWriter       = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $fileName = '房源错误模板导出';

        $objPHPExcel->setActiveSheetIndex(0);//设置工作sheet
        $objPHPExcel->getActiveSheet()->setTitle($fileName); //设置工作表名称
        $title = ['A1' => '楼宇id', 'B1' => '楼栋', 'C1' => '楼层', 'D1' => '房号', 'E1' => '面积（㎡）', 'F1' => '房租（元/月）',
                  'G1' => '物业（元/㎡）', 'H1' => '水（元/吨）', 'I1' => '电（元/度）', 'J1' => '公摊水（元/吨）', 'K1' => '公摊电（元/度）',
                  'L1' => '其他（元/月）', 'M1' => '错误提示'];
        $width = ['A' => 15, 'B' => 15, 'C' => 15, 'D' => 15, 'E' => 15, 'F' => 15, 'G' => 15, 'H' => 15, 'I' => 15, 'J' => 15, 'K' => 15, 'L' => 15, 'M' => 50];

        $objPHPExcel->getActiveSheet()->getStyle( 'A1:G1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
        foreach ($width as $a => $b) {//设置每列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($a)->setWidth($b);
        }
        foreach ($title as $c => $d) {//设置列名
            $objPHPExcel->getActiveSheet()->setCellValue($c, $d);
        }
        //合并第二行单元格
        $objPHPExcel->getActiveSheet()->mergeCells('A2:L2');
        //设置备注
        $comment = '备注（导入时请不要删除这栏）：1. 红色为必填项';
        $objPHPExcel->getActiveSheet()->setCellValue('A2', $comment);

        //导入具体数据
        $num=2;
        if ($accessData) {
            foreach ($accessData as $a => $b) {
                if ($b) {
                    ++$num;
                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $b[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $b[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $b[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $b[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $b[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $b[5]);
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $b[6]);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $b[7]);
                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $b[8]);
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$num, $b[9]);
                    $objPHPExcel->getActiveSheet()->setCellValue('K'.$num, $b[10]);
                    $objPHPExcel->getActiveSheet()->setCellValue('L'.$num, $b[11]);
                }
            }
        }

        if ($wrongData) {
            foreach ($wrongData as $c => $d) {
                if ($d) {
                    ++$num;
                    //错误的记录设置背景色
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$num.':'.'k'.$num)->getFill()
                        ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                        ->getStartColor()->setRGB('FFFF00');

                    $objPHPExcel->getActiveSheet()->setCellValue('A'.$num, $d[0]);
                    $objPHPExcel->getActiveSheet()->setCellValue('B'.$num, $d[1]);
                    $objPHPExcel->getActiveSheet()->setCellValue('C'.$num, $d[2]);
                    $objPHPExcel->getActiveSheet()->setCellValue('D'.$num, $d[3]);
                    $objPHPExcel->getActiveSheet()->setCellValue('E'.$num, $d[4]);
                    $objPHPExcel->getActiveSheet()->setCellValue('F'.$num, $d[5]);
                    $objPHPExcel->getActiveSheet()->setCellValue('G'.$num, $d[6]);
                    $objPHPExcel->getActiveSheet()->setCellValue('H'.$num, $d[7]);
                    $objPHPExcel->getActiveSheet()->setCellValue('I'.$num, $d[8]);
                    $objPHPExcel->getActiveSheet()->setCellValue('J'.$num, $d[9]);
                    $objPHPExcel->getActiveSheet()->setCellValue('K'.$num, $d[10]);
                    $objPHPExcel->getActiveSheet()->setCellValue('L'.$num, $d[11]);
                    $objPHPExcel->getActiveSheet()->setCellValue('M'.$num, $d['comment']);
                }
            }
        }


        $fileName = $fileName.date('Ymd_His').'.xls';
        $year = date('Y');
        $month = date('m');
        $day = date('d');
        $time = $year.'/'.$month.'/'.$day;
        $pathStr = EXCEL_IMPORT_DIR.'house/wrong/' . $time;
        $downloadStr = MAIN_DOMAIN.'excel/house/wrong/' . $time;
        createDir($pathStr);
        $objWriter->save($pathStr.'/'.$fileName);
        return $downloadStr.'/'.$fileName;
    }
}